import logging
from dataclasses import field
from datetime import datetime
from typing import List, Optional

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import EncryptionMiddleware, oauth2_scheme

air_alarm_router = APIRouter(prefix="/alarmSet", tags=["空气报警设定管理"])


class AirSearch(PaginationBase):
    fsiteNo: str = Field(None, description="站点编号")
    fairitem: str = Field(None, description="空气项目")


# todo 12.空气报警设置
@air_alarm_router.get("/airAlarm/list", summary="查询空气报警设定")
def list_air_alarm(air: AirSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from a_ts_airwarring where 1=1"
    if air.fsiteNo is not None:
        sql = sql + " and fsite_no= :fsiteNo "
        param["fsiteNo"] = air.fsiteNo
    if air.fairitem is not None:
        sql = sql + " and fairitem= :fairitem "
        param["fairitem"] = air.fairitem
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fwarringno,fsite_no,fscore_flag,fairitem,fbegnum,fendnum,fdescribe,"
    sql = sql + " flevelno,fsendmsg,fsendemail,fstate,foperator,fupddate"
    sql = sql + " from a_ts_airwarring where 1=1"
    if air.fsiteNo is not None:
        sql = sql + " and fsite_no= :fsiteNo "
        param["fsiteNo"] = air.fsiteNo
    if air.fairitem is not None:
        sql = sql + " and fairitem= :fairitem "
        param["fairitem"] = air.fairitem
    sql = sql + " limit :limit offset :offset "
    param["offset"] = air.offset
    param["limit"] = air.limit
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fwarringno,b.fentryid,b.fpoint_no fpointno,c.fpoint_name fpointName"
    sql_3 = sql_3 + " from a_ts_airwarring a,a_ts_airwarring_point b,a_ts_point c "
    sql_3 = sql_3 + " where a.fwarringno=b.fwarringno "
    sql_3 = sql_3 + " and b.fpoint_no=c.fpoint_no"
    if air.fsiteNo is not None:
        sql_3 = sql_3 + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = air.fsiteNo
    if air.fairitem is not None:
        sql_3 = sql_3 + " and a.fairitem= :fairitem "
        param["fairitem"] = air.fairitem
    sql_3 = sql_3 + " order by b.fwarringno,b.fentryid"
    data_detail = db.execute(text(sql_3), param).fetchall()

    # userlist 明细列表
    param = {}
    sql_2 = " select b.fwarringno,b.fentryid,b.fuserno,c.username"
    sql_2 = sql_2 + " from a_ts_airwarring a,a_ts_airwarring_user b,am_sys_user c "
    sql_2 = sql_2 + " where a.fwarringno=b.fwarringno "
    sql_2 = sql_2 + " and b.fuserno=c.id::text"
    if air.fsiteNo is not None:
        sql_2 = sql_2 + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = air.fsiteNo
    if air.fairitem is not None:
        sql_2 = sql_2 + " and a.fairitem= :fairitem "
        param["fairitem"] = air.fairitem
    sql_2 = sql_2 + " order by b.fwarringno,b.fentryid"
    data_userlist = db.execute(text(sql_2), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fwarringno'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fscoreFlag'] = data[key][2]
        d1['fairitem'] = data[key][3]
        d1['fbegnum'] = data[key][4]
        d1['fendnum'] = data[key][5]
        d1['fdescribe'] = data[key][6]
        d1['flevelno'] = data[key][7]
        d1['fsendmsg'] = data[key][8]
        d1['fsendemail'] = data[key][9]
        d1['fstate'] = data[key][10]
        d1['foperator'] = data[key][11]
        d1['fupddate'] = data[key][12]
        sbillno = data[key][0]
        data_list.append(d1)

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fwarringno'] = data_detail[j1][0]
                d2['fentryid'] = data_detail[j1][1]
                d2['fpointno'] = data_detail[j1][2]
                d2['fpointName'] = data_detail[j1][3]
                detail_list.append(d2)

        detail_list2 = []
        for j2 in range(len(data_userlist)):
            if (str(data_userlist[j2][0]) == str(sbillno)):
                d3 = {}
                d3['fwarringno'] = data_userlist[j2][0]
                d3['fentryid'] = data_userlist[j2][1]
                d3['fuserno'] = data_userlist[j2][2]
                d3['username'] = data_userlist[j2][3]
                detail_list2.append(d3)

        d1["atsPoints"] = detail_list
        d1["atsUsers"] = detail_list2

    info_dict = {"list": data_list,
                 "limit": air.limit,
                 "page": air.pageIndex,
                 "pages": (count + air.limit - 1) // air.limit,
                 "total": count
                 }
    return BaseResponse.success(data=info_dict)


class AirWarringCreate(BaseModel):
    """空气报警设定创建模型"""
    fsiteNo: str = Field(..., description="站点编号", min_length=1, max_length=50)
    fscoreFlag: int = Field(..., description="分数标识（1:启用，0:停用）")
    fairitem: int = Field(..., description="监测项目（如：1-甲醛，2-PM2.5）")
    fbegnum: float = Field(..., description="起始数值")
    fendnum: float = Field(..., description="结束数值")
    fdescribe: str = Field(..., description="报警描述", min_length=1, max_length=200)
    flevelno: int = Field(..., description="报警级别（1-5级）")
    fsendmsg: int = Field(..., description="是否发送短信（1:是，0:否）")
    fsendemail: int = Field(..., description="是否发送邮件（1:是，0:否）")
    fstate: int = Field(1, description="报警状态（1:启用，0:停用）", ge=0, le=1)

    # 嵌套子模型：用户列表和教室列表
    atsUsers: List[int] = Field(..., description="用户ID列表")
    atsPoints: List[str] = Field(..., description="教室点位编号列表")

    # 自动生成字段（由业务层填充）
    fwarringno: Optional[int] = Field(None, description="报警单号")
    foperator: Optional[str] = Field(None, description="操作人ID")
    fupddate: Optional[datetime] = Field(None, description="更新时间")


class AirWarringUser(BaseModel):
    """空气报警关联用户模型"""
    fwarringno: int = Field(..., description="报警单号")
    fuserno: int = Field(..., description="用户ID")


class AirWarringPoint(BaseModel):
    """空气报警关联点位模型"""
    fwarringno: int = Field(..., description="报警单号")
    fpoint_no: str = Field(..., description="点位编号")


@air_alarm_router.post("/airAlarm/create", summary="新增空气报警设定")
def create_air_alarm(air: AirWarringCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = 3
        if token is not None:
            user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            # 先取单号
            fwarringno = 0
            sql = "select nextval('a_ts_airwarring_fwarringno_seq') as last_value"
            res = db.execute(text(sql)).fetchall()
            fwarringno = res[0][0]

            # 返回内容
            d1 = {}
            sql = "insert into a_ts_airwarring(fwarringno,fsite_no,fscore_flag,fairitem,fbegnum,fendnum,fdescribe,flevelno,fsendmsg,fsendemail,fstate,foperator,fupddate)"
            sql = sql + " values( :fwarringno , :fsiteNo , :fscoreFlag , :fairitem , :fbegnum , :fendnum , :fdescribe , :flevelno , :fsendmsg , :fsendemail , :fstate , :userid ,now());"
            db.execute(text(sql), {
                "fwarringno": fwarringno,
                "fsiteNo": air.fsiteNo,
                "fscoreFlag": air.fscoreFlag,
                "fairitem": air.fairitem,
                "fbegnum": air.fbegnum,
                "fendnum": air.fendnum,
                "fdescribe": air.fdescribe,
                "flevelno": air.flevelno,
                "fsendmsg": air.fsendmsg,
                "fsendemail": air.fsendemail,
                "fstate": air.fstate,
                "userid": user_id,
            })

            # 人员列表
            Users = air.atsUsers
            for key in Users:
                sql = "insert into a_ts_airwarring_user(fwarringno,fuserno)"
                sql = sql + " values( :fwarringno , :fuserno );"
                db.execute(text(sql), {
                    "fwarringno": fwarringno,
                    "fuserno": key,
                })

            # 教室列表

            Points = air.atsPoints

            for key in Points:
                sql = "insert into a_ts_airwarring_point(fwarringno,fpoint_no)"
                sql = sql + " values( :fwarringno , :fpointNo );"
                db.execute(text(sql), {
                    "fwarringno": fwarringno,
                    "fpointNo": key
                })

            # 返回
            d1["fairitem"] = air.fairitem
            d1["fbegnum"] = air.fbegnum
            d1["fdescribe"] = air.fdescribe
            d1["fendnum"] = air.fendnum
            d1["flevelno"] = air.flevelno
            d1["fscoreFlag"] = air.fscoreFlag
            d1["fsendemail"] = air.fsendemail
            d1["fsendmsg"] = air.fsendmsg
            d1["fsiteNo"] = air.fsiteNo
            d1["fstate"] = air.fstate
            d1["fwarringno"] = fwarringno
            d1["fupddate"] = None
            d1["atsUsers"] = Users
            d1["atsPoints"] = Points
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@air_alarm_router.post("/airAlarm/update", summary="修改空气报警设定")
def update_air_alarm(air: AirWarringCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update a_ts_airwarring set fscore_flag= :fscoreFlag "
            sql = sql + ",fairitem= :fairitem ,fbegnum= :fbegnum ,fendnum= :fendnum "
            sql = sql + ",fdescribe= :fdescribe ,flevelno= :flevelno ,fsendmsg= :fsendmsg "
            sql = sql + ",fsendemail= :fsendemail ,fstate= :fstate "
            sql = sql + ",foperator= :userid ,fupddate=now()"
            sql = sql + " where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fairitem": air.fairitem,
                "fbegnum": air.fbegnum,
                "fendnum": air.fendnum,
                "fdescribe": air.fdescribe,
                "flevelno": air.flevelno,
                "fsendmsg": air.fsendmsg,
                "fsendemail": air.fsendemail,
                "fstate": air.fstate,
                "userid": user_id,
                "fwarringno": air.fwarringno,
            })

            # 人员列表
            sql = "delete from a_ts_airwarring_user where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fwarringno": air.fwarringno,
            })

            Users = []
            # 人员列表
            Users = air.atsUsers
            for key in Users:
                sql = "insert into a_ts_airwarring_user(fwarringno,fuserno)"
                sql = sql + " values( :fwarringno , :fuserno );"
                db.execute(text(sql), {
                    "fwarringno": air.fwarringno,
                    "fuserno": key,
                })

            # 教室列表
            sql = "delete from a_ts_airwarring_point where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fwarringno": air.fwarringno,
            })

            Points = []
            Points = air.atsPoints

            for key in Points:
                sql = "insert into a_ts_airwarring_point(fwarringno,fpoint_no)"
                sql = sql + " values( :fwarringno , :fpointNo );"
                db.execute(text(sql), {
                    "fwarringno": air.fwarringno,
                    "fpointNo": key
                })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@air_alarm_router.post("/airAlarm/delete/{fwarringno}", summary="删除空气报警设定")
def delete_air_alarm(fwarringno: int = Path(description="报警设定id"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = " select fwarringno from a_tc_airwarring where fwarringno= :fwarringno limit 3"
            data = db.execute(text(sql), {
                "fwarringno": fwarringno,
            }).fetchall()
            if len(data) > 0:
                return BaseResponse.error(message="该资料在使用，不能删除")
            else:
                sql = "delete from a_ts_airwarring where fwarringno= :fwarringno "
                db.execute(text(sql), {
                    "fwarringno": fwarringno
                })
                sql = "delete from a_ts_airwarring_point where fwarringno= :fwarringno "
                db.execute(text(sql), {
                    "fwarringno": fwarringno
                })
                sql = "delete from a_ts_airwarring_user where fwarringno= :fwarringno "
                db.execute(text(sql), {
                    "fwarringno": fwarringno
                })
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@air_alarm_router.get("/airAlarm/listUserByFsiteNo/{id}", summary="获取属于该组织的用户")
def get_user_by_fsiteNo(id: int = Path(), db: Session = Depends(get_db)):
    sql = "select id fuserno,username from am_sys_user "
    sql = sql + " where fsite_no_belong= :id"
    sql = sql + " and status=1"
    sql = sql + " order by id"
    roledata = db.execute(text(sql), {
        "id": id,
    }).fetchall()
    data_list = []
    for key in range(len(roledata)):
        d2 = {}
        d2['fuserno'] = roledata[key][0]
        d2['username'] = roledata[key][1]
        data_list.append(d2)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@air_alarm_router.get("/airAlarm/listPointByFsiteNo/{id}", summary="获取属于该组织的教室")
def get_point_by_fsiteNo(id: int = Path(description="客户编号"), db: Session = Depends(get_db)):
    sql = "select fpoint_no as fpointNo,fpoint_name as fpointName from a_ts_point "
    sql = sql + " where fsite_no= :id "
    sql = sql + " and fpoint_flag=0"
    sql = sql + " and fpoint_state=1"
    sql = sql + " order by fpoint_no"
    roledata = db.execute(text(sql), {
        "id": id,
    }).fetchall()
    data_list = []
    for key in range(len(roledata)):
        d2 = {}
        d2['fpointNo'] = roledata[key][0]
        d2['fpointName'] = roledata[key][1]
        data_list.append(d2)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


class AlarmSearch(PaginationBase):
    begdate: datetime = Field("2000-01-01", description="开始时间")
    enddate: datetime = Field("2000-01-01", description="结束时间")
    fsiteNo: str = Field(None, description="站点id")
    fairitem: str = Field(None, description="")


@air_alarm_router.get("/alarmInfo/list", summary="查询空气报警")
def get_alarm_info(alarm: AlarmSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from a_tc_airwarring a"
    sql = sql + " left join a_ts_site b on a.fsite_no=b.fsite_no"
    sql = sql + " left join a_ts_point c on a.fpoint_no=c.fpoint_no"
    sql = sql + " left join a_ts_site d on b.fsite_parent=d.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fgetdate,'yyyymmdd') between :begdate "
    sql = sql + " and :enddate "
    param["begdate"] = alarm.begdate
    param["enddate"] = alarm.enddate
    if alarm.fsiteNo is not None:
        sql = sql + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = alarm.fsiteNo
    if alarm.fairitem is not None:
        sql = sql + " and a.fairitem= :fairitem "
        param["fairitem"] = alarm.fairitem
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.fairitem,a.fgetdate,d.fsite_name as fareaName,b.fsite_name,c.fpoint_name"
    sql = sql + ",a.fcurnum,a.fbegnum,a.fendnum,a.fdescribe,a.fenddate"
    sql = sql + ",case a.fsendmsg when 0 then '未发送' else '已发送' end fsendmsg"
    sql = sql + ",case a.fsendemail when 0 then '未发送' else '已发送' end fsendemail"
    sql = sql + " from a_tc_airwarring a"
    sql = sql + " left join a_ts_site b on a.fsite_no=b.fsite_no"
    sql = sql + " left join a_ts_point c on a.fpoint_no=c.fpoint_no"
    sql = sql + " left join a_ts_site d on b.fsite_parent=d.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fgetdate,'yyyymmdd') between :begdate "
    sql = sql + " and :enddate "
    param["begdate"] = alarm.begdate
    param["enddate"] = alarm.enddate
    if alarm.fsiteNo is not None:
        sql = sql + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = alarm.fsiteNo
    if alarm.fairitem is not None:
        sql = sql + " and a.fairitem= :fairitem "
        param["fairitem"] = alarm.fairitem
    sql = sql + " limit :limit offset :offset "
    param["limit"] = alarm.limit
    param["offset"] = alarm.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['begdate'] = None
        d1['days'] = 0
        d1['email'] = None
        d1['enddate'] = None
        d1['fairitem'] = data[key][0]
        d1['fairitemName'] = None
        d1['fareaName'] = data[key][2]
        d1['fbegnum'] = data[key][6]
        d1['fcurnum'] = data[key][5]
        d1['fdescribe'] = data[key][8]
        d1['fenddate'] = data[key][9]
        d1['fendnum'] = data[key][7]
        d1['fentryid'] = None
        d1['fgetdate'] = data[key][1]
        d1['fpointName'] = data[key][4]
        d1['fPointNo'] = None
        d1['fprocflag'] = None
        d1['fsendmsg'] = None
        # d1['fsendmsgStr'] = data[key]['fsendmsg']
        # d1['fsiteName'] = data[key]['fsite_name']
        d1['fsiteNo'] = None
        d1['fwarringno'] = None
        d1['phone'] = None
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)
